﻿namespace LeadMaster.Controllers
{
    using System;
    using System.Collections.Generic;
    using System.Data.Entity;
    using System.Linq;
    using System.Web;
    using System.Web.Mvc;
    using LeadMaster.Models;
    using LeadMaster.DAL;
    using LeadMaster.ViewModels;
  	using System.Web.Security;
    using PagedList;
  	using LeadMaster.Helpers;
    using System.Net;
    using System.Net.Mail;
    using System.Configuration;

  [Authorize]
    public class LeadsController : Controller
    {
        private DunsmoreContext db = new DunsmoreContext();
        private string Surname;
        private string Postcode;

        public ActionResult Index(int? page)
        {
            ViewModelLeads viewModel = new ViewModelLeads();
            IEnumerable<Leads> leads = db.Leads;
            
            viewModel.Leads=leads
                .OrderByDescending(m => m.ActionRequired)
                .ThenBy(m => m.LeadStatuses.DisplayOrder)
                .ToList();

            viewModel.UserFilter = string.Empty;
            
            if (Request.HttpMethod!="GET")
            {
                page = 1;
            }
            int pageNumber = (page ?? 1);
            var onePageOfLeads = viewModel.Leads.ToPagedList(pageNumber, 30);

            ViewBag.LeadsPage = onePageOfLeads;
            ViewBag.LeadStatusesID = new SelectList(db.LeadStatuses, "LeadStatusesID", "LeadStatus");

            return PartialView(viewModel);
        }

        public ActionResult PanelIndex()
        {
            if (db.Leads.Count() > 0)
            {
                List<LeadIndex> LeadIndexes = new List<LeadIndex>();
                List<int> LeadIds = db.Leads.OrderBy(m => m.LeadStatusesID).Select(m => m.LeadID).ToList();
                foreach (int ID in LeadIds)
                {
                    LeadIndex leadIndex = new LeadIndex();
                    Leads lead = db.Leads.Find(ID);
                    if (lead != null)
                    {
                        // got a lead for this id
                        // is it a Client or Outsource
                        if (lead.Category == "C")
                        {
                            leadIndex.ClientID = lead.LeadID;
                            leadIndex.Client = lead.Company.Company;
                            leadIndex.Description = lead.Description;
                            leadIndex.LeadStatus = lead.LeadStatuses.LeadStatus;
                            // have we an Outsource for this
                            lead = db.Leads.Where(m => m.LeadRef == leadIndex.ClientID).FirstOrDefault();
                            leadIndex.Outsourcer = lead.Company.Company;
                            leadIndex.OutsourcerID = lead.LeadID;
                        }
                        else
                        {
                            leadIndex.OutsourcerID = lead.LeadID;
                            leadIndex.Outsourcer = lead.Company.Company;
                            leadIndex.Description = lead.Description;
                            leadIndex.LeadStatus = lead.LeadStatuses.LeadStatus;
                            // have we a Client for this
                            lead = db.Leads.Where(m => m.LeadRef == leadIndex.OutsourcerID).FirstOrDefault();
                            leadIndex.Client = lead.Company.Company;
                            leadIndex.ClientID = lead.LeadID;
                        }
                        LeadIndexes.Add(leadIndex);
                    }

                }
                return PartialView("Index",LeadIndexes);
            }
            return PartialView();
        }

        public ActionResult IndexLeads(string leadStatusID)
        {
            int iLeadStatusID = Convert.ToInt32(leadStatusID);
            ViewBag.Title = db.LeadStatuses.Find(iLeadStatusID).LeadStatus;
            if (db.Leads.Count() > 0)
            {
                return View("Index",db.Leads.Where(m => m.LeadStatusesID == iLeadStatusID).ToList());
            }
            return View("Index");
        }

        public ActionResult Details(int id = 0)
        {
            Leads leads = db.Leads.Find(id);
            if (leads == null)
            {
                return HttpNotFound();
            }
            return View(leads);
        }

        public ActionResult Create()
        {
            ViewModelLeads viewModel = new ViewModelLeads();
            viewModel.Lead = new Leads();
            ViewBag.SectorID = new SelectList(db.Sectors.OrderBy(m => m.Sector), "SectorID", "Sector");
            ViewBag.LeadStatusesID = new SelectList(db.LeadStatuses.OrderBy(l => l.LeadStatusesID), "LeadStatusesID", "LeadStatus");
            ViewBag.SalutationID = new SelectList(db.Salutations, "SalutationID", "Salutation");
            viewModel.Lead.InitialContactDate = DateTime.Now.Date;
            viewModel.Lead.ContactType = "T";
            viewModel.Lead.Category = "C";
			      viewModel.Company=new Companies();
			      viewModel.Contact=new Contacts();
            viewModel.InitialContactDate = DateTime.Now.ToString("d");

            viewModel.CommencementDate = DateTime.Now.ToString("d");
			      viewModel.Lead.TotalWeeks = 1;
            viewModel.NextContactDate = DateTime.Now.ToShortDateString();
            return PartialView(viewModel);
        }

        [HttpPost]
        public ActionResult Create(ViewModelLeads viewModel)
        {
            try
            {
                //save new company
                db.Companies.Add(viewModel.Company);
                db.SaveChanges();
                //save new contact
                viewModel.Contact.CompanyID = viewModel.Company.CompanyID;
                viewModel.Contact.DefaultTelephoneField = "Telephone";
                db.Contacts.Add(viewModel.Contact);
                db.SaveChanges();
                viewModel.Lead.CompanyID = viewModel.Company.CompanyID;
                viewModel.Lead.ContactID = viewModel.Contact.ContactID;
                viewModel.Lead.InitialContactDate = DateTime.Now;
                viewModel.Lead.CommencementDate = DateTime.Now;
                viewModel.Lead.LeadStatusesID = 2;  // set to Lead 
                viewModel.Lead.LastActionBy = (Guid)Membership.GetUser().ProviderUserKey;
                viewModel.Lead.UserID = (Guid)Membership.GetUser().ProviderUserKey;
                viewModel.Lead.CallCentre1ID = 1;
                viewModel.Lead.ReturnDays = 4;
                db.Leads.Add(viewModel.Lead);
                db.SaveChanges();
            }
            catch (Exception e)
            {
                ViewBag.ErrorMessage = "Create Lead " + e.LineNumber().ToString() + " : " + e.Message + " - " + e.InnerException;
                return View("Errormessages");
            }
            try
            {
                // first Enquiry noted
                viewModel.Enquiry = new Enquiries();
                viewModel.Enquiry.LeadID = viewModel.Lead.LeadID;
                viewModel.Enquiry.Notes = "Initial Contact." + viewModel.Lead.FreeNotes;
                viewModel.Enquiry.CompanyID = viewModel.Lead.CompanyID;
                viewModel.Enquiry.ContactID = viewModel.Lead.ContactID;
                viewModel.Enquiry.EnquiryTypeID = 1; // to be fulfulled at a later date
                viewModel.Enquiry.UserID = (Guid)Membership.GetUser().ProviderUserKey;
                viewModel.Enquiry.EnquiryDate = DateTime.Now;
                db.Enquiry.Add(viewModel.Enquiry);
                db.SaveChanges();
            }
            catch (Exception e)
            {
                db.Leads.Remove(viewModel.Lead);
                db.SaveChanges();
                ViewBag.ErrorMessage = "Create Lead " + e.LineNumber().ToString() + " : " + e.Message + " - " + e.InnerException;
                return View("Errormessages");
            }
            try
            {
                // Diary entry
                Diary leadDiary = new Diary();
                leadDiary.CompanyID = viewModel.Lead.CompanyID;
                leadDiary.ContactID = viewModel.Lead.ContactID;
                leadDiary.ContactType = "T";
                leadDiary.LeadID = viewModel.Lead.LeadID;
                leadDiary.UserID = (Guid)Membership.GetUser().ProviderUserKey;
                leadDiary.Scheduled = Convert.ToDateTime(viewModel.NextContactDate);
                leadDiary.Notes = viewModel.Lead.FreeNotes ?? "System generated note - please review previous engagement.";
                db.Diary.Add(leadDiary);
            }
            catch (Exception e)
            {
                db.Leads.Remove(viewModel.Lead);
                db.Enquiry.Remove(viewModel.Enquiry);
                db.SaveChanges();
                ViewBag.ErrorMessage = "Create Lead " + e.LineNumber().ToString() + " : " + e.Message + " - " + e.InnerException;
                return View("Errormessages");
            }
            try
            {
                // Trading Address
                TradingAddresses tradingAddress = new TradingAddresses();
                tradingAddress.CompanyID = viewModel.Company.CompanyID;
                tradingAddress.TradingAddress1 = "to be completed";
                db.TradingAddresses.Add(tradingAddress);
                db.SaveChanges();
                // Update Leads default Trading Address ID
                viewModel.Lead.TradingAddressID = tradingAddress.TradingAddressID;
                db.Entry(viewModel.Lead).State = EntityState.Modified;
                db.SaveChanges();
            }
            catch (Exception e)
            {
                db.Leads.Remove(viewModel.Lead);
                db.Enquiry.Remove(viewModel.Enquiry);
                db.SaveChanges();
                ViewBag.ErrorMessage = "Create Lead " + e.LineNumber().ToString() + " : " + e.Message + " - " + e.InnerException;
                return View("Errormessages");
            }

            return RedirectToAction("Index", "Home");
		}

        public ActionResult ContactsList(string companyID, string contactID, string viewName)
        {
            ViewModelLeads viewModel = new ViewModelLeads();
            if (contactID != string.Empty)
                viewModel.ContactID = Convert.ToInt32(contactID);

            int locCompanyID = Convert.ToInt32(companyID);

            Companies company = db.Companies.Find(locCompanyID);

            if (company != null)
            {
                company.Contacts = db.Contacts.Where(m => m.CompanyID == locCompanyID).ToList();
                viewModel.CompanyContacts = db.Contacts
                            .ToList()
                            .Where(m => m.CompanyID == locCompanyID)
                            .OrderBy(m => m.Surname)
                            .ThenBy(m => m.Forename)
                            .Select(c => new SelectListItem
                            {
                                Value = c.ContactID.ToString(),
                                Text = string.Format("{0} {1}", c.Forename, c.Surname)
                            });
            }
            else
            {
                viewModel.CompanyContacts = db.Contacts
                                            .ToList()
                                            .OrderBy(m => m.Surname)
                                            .ThenBy(m => m.Forename)
                                            .Select(c => new SelectListItem
                                            {
                                                Value = c.ContactID.ToString(),
                                                Text = string.Format("{0} {1}", c.Forename, c.Surname)
                                            });
            }
            return this.PartialView(viewName, viewModel);
        }

        public ActionResult Edit(int id, int? contactID, char editMode, char? code, string selectionStatus = null)
        {
            ViewModelLeads viewModel = new ViewModelLeads();
            viewModel.Lead = db.Leads.Find(id);
          
            ViewBag.CompanyID = new SelectList(db.Companies.Where(c => c.Category == "C").OrderBy(c => c.Company), "CompanyID", "Company", viewModel.Lead.CompanyID);
            ViewBag.SectorID = new SelectList(db.Sectors.OrderBy(m => m.Sector), "SectorID", "Sector",viewModel.Lead.SectorID);
            ViewBag.LeadsReplacementID = new SelectList(db.LeadsReplacement, "LeadsReplacementID", "Title",viewModel.Lead.LeadsReplacementID);
            ViewBag.PaymentTermsID = new SelectList(db.PaymentTerms, "PaymentTermsID", "Title");
            ViewBag.InvoicingID = new SelectList(db.Invoicing, "InvoicingID", "Title");
            ViewBag.ScheduleTemplateID = new SelectList(db.ScheduleTemplate, "ScheduleTemplateID", "Title");
            ViewBag.EnquiryTypeID = new SelectList(db.EnquiryTypes, "EnquiryTypeID", "EnquiryType");
            ViewBag.LeadStatusesID = new SelectList(db.LeadStatuses.OrderBy(l => l.LeadStatusesID), "LeadStatusesID", "LeadStatus",viewModel.Lead.LeadStatusesID);
            ViewBag.SalutationID = new SelectList(db.Salutations, "SalutationID", "Salutation");
            ViewBag.TermConditionsID = new SelectList(db.TermConditions, "TermConditionsID", "Title");
            viewModel.CallCentres = new MultiSelectList(db.CallCentres.Where(m => m.active==true), "CallCentreID", "CallCentre");
            ViewBag.LeadTemplateID = new SelectList(db.LeadTemplate, "LeadTemplateID", "Title");
            viewModel.EnquiryTypeID = db.Enquiry.Where(e => e.LeadID == viewModel.Lead.LeadID).Select(e => e.EnquiryTypeID).FirstOrDefault();

            viewModel.ClientContacts = db.Contacts.Where(m => m.CompanyID == viewModel.Lead.CompanyID).OrderBy(m => m.Forename).ThenBy(m => m.Surname).ToList();

            TradingAddresses tradingAddress = new TradingAddresses();


            viewModel.HoldingDate = viewModel.Lead.CommencementDate.ToString("dd/MM/yyyy");
            if (contactID!=null)
            {
                viewModel.ContactID = contactID ?? 0;
                viewModel.Contact = db.Contacts.Find(contactID);
            }
            else
            {
                viewModel.Contact = db.Contacts.Find(viewModel.Lead.ContactID);
            }

            viewModel.Company = db.Companies.Find(viewModel.Lead.CompanyID);
            viewModel.CompanyID = viewModel.Company.CompanyID;

            viewModel.Enquiries = db.Enquiry
                    .Where(e => e.LeadID == id)
                    .Include(e => e.Contact)
                    .Include(e => e.Company)
                    .OrderByDescending(m => m.EnquiryDate)
                    .ToList();

            viewModel.TradingAddresses = db.TradingAddresses.Where(m => m.CompanyID == viewModel.CompanyID).ToList();
            viewModel.Salutations = db.Salutations.ToList();

            if (editMode!=0)
            {
                viewModel.EditTab = editMode;
            }

            viewModel.ColumnFilter = code ?? 'D' ;    

            viewModel.UserFilter = selectionStatus != null ? selectionStatus : string.Empty;
            viewModel.SelectedCallCentres=new List<int>();
            if (viewModel.Lead.CallCentre1ID != 0) viewModel.SelectedCallCentres.Add(viewModel.Lead.CallCentre1ID);
            if (viewModel.Lead.CallCentre2ID != 0) viewModel.SelectedCallCentres.Add(viewModel.Lead.CallCentre2ID ?? 0);
            if (viewModel.Lead.CallCentre3ID != 0) viewModel.SelectedCallCentres.Add(viewModel.Lead.CallCentre3ID ?? 0);
            if (viewModel.Lead.CallCentre4ID != 0) viewModel.SelectedCallCentres.Add(viewModel.Lead.CallCentre4ID ?? 0);
            if (viewModel.Lead.CallCentre5ID != 0) viewModel.SelectedCallCentres.Add(viewModel.Lead.CallCentre5ID ?? 0);

            return PartialView(viewModel);
        }

        [HttpPost]
        public void Edit(ViewModelLeads viewModel)
        {
            int iCounter = 0;
            string tradingStreet = string.Empty;
            try
            {
            if (viewModel.TradingAddresses!=null)
            {
              foreach (TradingAddresses tradingAddress in viewModel.TradingAddresses)
              {
                if (!db.TradingAddresses.Any(m => m.TradingAddressID == tradingAddress.TradingAddressID))
                {
                  // user has entered a trading address
                  if (tradingAddress.TradingAddress1 != null)
                  {
                    tradingAddress.CompanyID = viewModel.Lead.Company.CompanyID;
                    db.TradingAddresses.Add(tradingAddress);
                    db.SaveChanges();
                  }
                }
                else
                {
                  TradingAddresses updatedTradingAddress = db.TradingAddresses.Find(tradingAddress.TradingAddressID);
                  updatedTradingAddress.TradingAddress1 = tradingAddress.TradingAddress1;
                  updatedTradingAddress.TradingAddress2 = tradingAddress.TradingAddress2;
                  updatedTradingAddress.TradingCounty = tradingAddress.TradingCounty;
                  updatedTradingAddress.TradingDistrict = tradingAddress.TradingDistrict;
                  updatedTradingAddress.TradingPostcode = tradingAddress.TradingPostcode;
                  updatedTradingAddress.TradingTown = tradingAddress.TradingTown;
                  db.Entry(updatedTradingAddress).State = EntityState.Modified;
                }
                if (iCounter == viewModel.DefaultTradingAddressID)
                {
                  viewModel.Lead.TradingAddressID = tradingAddress.TradingAddressID;
                }

                  iCounter++;
              }
            }
            // Add Contact for the company
            if (viewModel.ClientContacts!=null)
            {
              foreach (Contacts contact in viewModel.ClientContacts)
              {
                if (db.Contacts.Any(m => m.ContactID==contact.ContactID))
                {
                  db.Entry(contact).State = EntityState.Modified;  
                }
                else
                {
                  db.Contacts.Add(contact);
                }
              }
            }

            db.SaveChanges();

            if (viewModel.ContactID==0)
            {
                viewModel.ContactID = viewModel.ClientContacts.FirstOrDefault().ContactID;
            }
            viewModel.Lead.Company = viewModel.Company;
            db.Entry(viewModel.Company).State = EntityState.Modified;
            if (ModelState.IsValid)
            {
                db.SaveChanges();
            }

            viewModel.Lead.ContactID = viewModel.ContactID;
            //viewModel.Lead.NotifyEmailID = viewModel.NotifyEmailID;
            viewModel.Lead.LeadStatusesID = 1;
            viewModel.Lead.ReceptiveID = 1;
            viewModel.Lead.CompanyID = viewModel.Lead.Company.CompanyID;
            viewModel.Lead.LastActionBy = (Guid)Membership.GetUser().ProviderUserKey;
            viewModel.Lead.CommencementDate = Convert.ToDateTime(viewModel.HoldingDate);
            Leads currentLead = db.Leads.Find(viewModel.Lead.LeadID);
            int holdingStatus = currentLead.LeadStatusesID;
            currentLead.ContactID = viewModel.Lead.ContactID;
            currentLead.NotifyEmailID = viewModel.Lead.NotifyEmailID;
            currentLead.InitialContactDate = viewModel.Lead.InitialContactDate;
            currentLead.ContactType = viewModel.Lead.ContactType;
            currentLead.FreeNotes = viewModel.Lead.FreeNotes;
            currentLead.LeadStatusesID = viewModel.Lead.LeadStatusesID;
            currentLead.ReceptiveID = viewModel.Lead.ReceptiveID;
            currentLead.CompanyID = viewModel.Lead.CompanyID;
            currentLead.Description = viewModel.Lead.Description;
            currentLead.CommencementDate = Convert.ToDateTime(viewModel.HoldingDate);
            currentLead.Territory = (viewModel.Lead.Territory ?? string.Empty).ToUpper();
            currentLead.LeadRef = viewModel.Lead.LeadRef;
            currentLead.Category = viewModel.Lead.Category;
            currentLead.TotalWeeks = viewModel.Lead.TotalWeeks;
            currentLead.LeadsReplacementID = viewModel.Lead.LeadsReplacementID;
            currentLead.TermConditionsID = viewModel.Lead.TermConditionsID;
            currentLead.ScheduleTemplateID = viewModel.Lead.ScheduleTemplateID;
            currentLead.PaymentTermsID = viewModel.Lead.PaymentTermsID;
            currentLead.InvoicingID = viewModel.Lead.InvoicingID;
            currentLead.Guarantee = viewModel.Lead.Guarantee;
            currentLead.InvoiceInsuranceLimit = viewModel.Lead.InvoiceInsuranceLimit;
            currentLead.TradingAddressID = viewModel.Lead.TradingAddressID;
            currentLead.LastActionBy = (Guid)Membership.GetUser().ProviderUserKey;
            currentLead.UserID = (Guid)Membership.GetUser().ProviderUserKey;
            currentLead.FeesOther = viewModel.Lead.FeesOther;
            currentLead.LeadStatusesID = viewModel.LeadStatusesID;
            currentLead.IntRef = viewModel.Lead.IntRef!=null ? viewModel.Lead.IntRef.ToUpper() : string.Empty;
            currentLead.LeadTemplateID = viewModel.Lead.LeadTemplateID;
            currentLead.Combination = viewModel.Lead.Combination;
            currentLead.CallCentre2ID = null;
            currentLead.CallCentre3ID = null;
            currentLead.CallCentre4ID = null;
            currentLead.CallCentre5ID = null;
            currentLead.ReturnDays = viewModel.Lead.ReturnDays;
            currentLead.CreditBased = viewModel.Lead.CreditBased;
            currentLead.StreetView = viewModel.Lead.StreetView;
            currentLead.EPC = viewModel.Lead.EPC;
            currentLead.DunsmorePreCheck = true;  // surveys always go through Dunsmore QC
            currentLead.NewLeadAlert = viewModel.Lead.NewLeadAlert;
            currentLead.bulletpoint = viewModel.Lead.bulletpoint;
            currentLead.SectorID = viewModel.Sector.SectorID;
            int iCC = 1;
            if (viewModel.SelectedCallCentres!=null)
            {
            foreach (int item in viewModel.SelectedCallCentres)
            {
              switch (iCC)
              {
                case 1:
                  currentLead.CallCentre1ID = item;
                  break;
                case 2:
                  currentLead.CallCentre2ID = item;
                  break;
                case 3:
                  currentLead.CallCentre3ID = item;
                  break;
                case 4:
                  currentLead.CallCentre4ID = item;
                  break;
                case 5:
                  currentLead.CallCentre5ID = item;
                  break;
                default:
                  break;
              }
              iCC++;
            }
            }
            db.Entry(currentLead).State = EntityState.Modified;

            db.SaveChanges();

            db.SaveChanges();
            }
            catch (Exception e)
            {
                ViewBag.ErrorMessage = e.LineNumber().ToString() + " : " + e.Message;
            }

        }

        public ActionResult Delete(int id = 0)
        {
            Leads leads = db.Leads.Find(id);
            if (leads == null)
            {
                return HttpNotFound();
            }
            return View(leads);
        }

        [HttpPost, ActionName("Delete")]
        public ActionResult DeleteConfirmed(int id)
        {
            Leads leads = db.Leads.Find(id);
            db.Leads.Remove(leads);
            db.SaveChanges();
            return RedirectToAction("Index");
        }

        public ActionResult Diary(string leadID)
        {
            Leads lead = db.Leads.Find(Convert.ToInt32(leadID));
            Diary diary = new Diary();
            diary.LeadID = lead.LeadID;
            diary.CompanyID=lead.CompanyID;
            diary.ContactID=lead.ContactID;
            diary.Scheduled = DateTime.Now;
            Contacts contact = db.Contacts.Find(lead.ContactID);
            ViewBag.contact = string.Format("{0} {1}", contact.Forename, contact.Surname);
            return PartialView("Diary", diary);
        }

        [HttpPost]
        public ActionResult Diary(Diary diary)
        {
            if (ModelState.IsValid)
            {
                db.Diary.Add(diary);
                db.SaveChanges();
                return Content("");
            }

            return PartialView(diary);
        }

        protected override void Dispose(bool disposing)
        {
            db.Dispose();
            base.Dispose(disposing);
        }

        public ActionResult GetContactID(int id)
        {
          
            Contacts contact=db.Contacts.Where(m => m.CompanyID==id).FirstOrDefault() ;
            if (contact==null)
            {
                return Json(0, JsonRequestBehavior.AllowGet);
            }
            return Json(contact.ContactID, JsonRequestBehavior.AllowGet);
        }

        public ActionResult TradingAddressID(int id)
        {

          TradingAddresses tradingAddress = db.TradingAddresses.Where(m => m.CompanyID == id).OrderBy(m => m.TradingTown).FirstOrDefault();
          if (tradingAddress==null)
          {
            return Json(0, JsonRequestBehavior.AllowGet);
          }
          return Json(tradingAddress.TradingAddressID, JsonRequestBehavior.AllowGet);
        }

        public static string GetColourCode(int leadID)
        {
            DunsmoreContext db = new DunsmoreContext();
            Leads lead = db.Leads.Find(leadID);
            
            return "color: #" + lead.LeadStatuses.Colourcode + ";" ;
        }

        public ActionResult Resort(char code, string selectionStatus = null, string sortDirection = null, int? page=1, bool ignoreDirection=false)
        {
            string companyCategory = Roles.IsUserInRole("Operations") ? "O" : "C";
            IQueryable<Leads> leads;
			ViewModelLeads viewModel = new ViewModelLeads();
			switch (code)
			{
				case 'L':
                    if (sortDirection == "u" && !ignoreDirection)
				{
                leads = from l in db.Leads
                        join s in db.Sectors on l.SectorID equals s.SectorID
                        orderby s.Sector descending
                        select l;                
					viewModel.ResortDirection = "d";
				}
				else 
				{
					leads = from l in db.Leads
					join s in db.Sectors on l.SectorID equals s.SectorID
					orderby s.Sector
					select l;                
					viewModel.ResortDirection = "u";
				}
				break;
				case 'C':
					if (sortDirection=="u" && !ignoreDirection)
					{
						leads = from l in db.Leads
								join c in db.Contacts on l.ContactID equals c.ContactID
								orderby c.Surname descending, c.Forename descending
								select l;
						viewModel.ResortDirection = "d";
					}
					else
					{
						leads = from l in db.Leads
								join c in db.Contacts on l.ContactID equals c.ContactID
								orderby c.Surname, c.Forename
								select l;
						viewModel.ResortDirection = "u";
					}
					break;
				case 'M':
                    if (sortDirection == "u" && !ignoreDirection)
					{
						leads = from l in db.Leads
		                    join c in db.Companies on l.CompanyID equals c.CompanyID
			                orderby c.Company descending
				            select l;
						viewModel.ResortDirection = "d";
					}
					else 
					{
						leads = from l in db.Leads
								join c in db.Companies on l.CompanyID equals c.CompanyID
								orderby c.Company
								select l;
						viewModel.ResortDirection = "u";
					}
					break;
				case 'R':
                    if (sortDirection == "u" && !ignoreDirection)
					{
	                leads = from l in db.Leads
		                    orderby l.Territory descending
			                select l;
						viewModel.ResortDirection = "d";
					}
					else 
					{
						leads = from l in db.Leads
								orderby l.Territory
								select l;
						viewModel.ResortDirection = "u";
					}
					break;
	            case 'V':
                    if (sortDirection == "u" && !ignoreDirection)
					{
						leads = from l in db.Leads
								join f in db.Fees on l.LeadID equals f.LeadID
								orderby f.Quantity*f.PriceEach*l.TotalWeeks descending
								select l;
						viewModel.ResortDirection = "d";
					}
					else 
					{
						leads = from l in db.Leads
								join f in db.Fees on l.LeadID equals f.LeadID
								orderby f.Quantity * f.PriceEach * l.TotalWeeks
								select l;
						viewModel.ResortDirection = "u";
					}
					break;
				case 'D':
                    if (sortDirection == "u" && !ignoreDirection)
					{
						leads = db.Leads.OrderByDescending(m => m.Diaries.OrderByDescending(o => o.Scheduled).FirstOrDefault().Scheduled);
						viewModel.ResortDirection = "d";
					}
					else 
					{
						leads = db.Leads.OrderBy(m => m.Diaries.OrderBy(o => o.Scheduled).FirstOrDefault().Scheduled);
						viewModel.ResortDirection = "u";
					}
					break;
				case 'S':
                    if (sortDirection == "u" && !ignoreDirection)
					{
					leads = from l in db.Leads
						    join ls in db.LeadStatuses on l.LeadStatusesID equals ls.LeadStatusesID
							orderby ls.DisplayOrder descending
							select l;
					viewModel.ResortDirection = "d";
					}
					else 
					{
						leads = from l in db.Leads
								join ls in db.LeadStatuses on l.LeadStatusesID equals ls.LeadStatusesID
								orderby ls.DisplayOrder
								select l;
						viewModel.ResortDirection = "u";
					}
					break;
				case 'U':
                    if (sortDirection == "u" && !ignoreDirection)
					{
                        leads = db.Leads.OrderByDescending(m => m.Users.UserName);
						viewModel.ResortDirection = "d";
					}
					else 
					{       
						leads = db.Leads.OrderBy(m => m.Users.UserName);         
						viewModel.ResortDirection = "u";
					}

					break;
				default:
                    if (sortDirection == "u" && !ignoreDirection)
					{
						leads = db.Leads.OrderByDescending(m => m.Diaries.FirstOrDefault().Scheduled);      
						viewModel.ResortDirection = "d";
					}
					else 
					{
						leads = db.Leads.OrderBy(m => m.Diaries.FirstOrDefault().Scheduled);
						viewModel.ResortDirection = "u";
					}          
					break;
			}
            
            if (selectionStatus != null)
            {
              switch (selectionStatus)
              {
                case "M":
                    Guid currentUser = (Guid)Membership.GetUser().ProviderUserKey;
                    leads = leads.Where(m => m.UserID == currentUser);
                    break;
                case "C":
                    leads = leads.Where(m => m.LeadStatusesID > 4);
                    break;
                case "S":
                    leads = leads.Where(m => m.LeadStatusesID <= 4);
                    break;
                default:
                  break;
              }
            }

            viewModel.Leads = leads.ToList();
            var query = "exec chasers";
            GenericRepository gr = new GenericRepository();
            viewModel.chasers = gr.GetChasers(query) as List<Chasers>;
            if (viewModel.chasers.Count() > 0)
            {
                foreach (var item in viewModel.Leads)
                {
                    Chasers chaser = viewModel.chasers.Where(m => m.IntRef == item.IntRef).FirstOrDefault();
                    if (chaser != null)
                    {
                        item.Company.Company += "*";
                    }
                }
            }
            viewModel.UserFilter = selectionStatus ?? "A";
            viewModel.ColumnFilter = code;
            ViewBag.LeadStatusesID = new SelectList(db.LeadStatuses, "LeadStatusesID", "LeadStatus");

            if (Request.HttpMethod != "GET")
            {
                page = 1;
            }
            int pageNumber = (page ?? 1);
            var onePageOfLeads = viewModel.Leads.ToPagedList(pageNumber, 30);

            ViewBag.LeadsPage = onePageOfLeads;

            return PartialView("Index", viewModel);
        }

      public ActionResult AddContact(int LeadID)
      {
        ViewModelLeads viewModel=new ViewModelLeads() ;
        viewModel.Lead=db.Leads.Find(LeadID);
        viewModel.Salutations = db.Salutations.ToList();
        Contacts newContact = new Contacts();
        newContact.CompanyID = viewModel.Lead.CompanyID;
        newContact.Forename = "New Contact";
        newContact.Surname = string.Empty;
        newContact.SalutationID = 1;  
        db.Contacts.Add(newContact);
        db.SaveChanges();
        viewModel.ClientContacts = db.Contacts.Where(m => m.CompanyID == viewModel.Lead.CompanyID).OrderBy(m => m.Forename).ThenBy(m => m.Surname).ToList();
        return PartialView("ClientContactsList", viewModel);
      }

      public ActionResult Filter(string filtertext, string cat)
      {
        ViewModelLeads viewModel = new ViewModelLeads();
        IEnumerable<Leads> leads ;
        filtertext = filtertext.Replace("_", " ");
        switch (cat)
        {
          case "company":
            leads = db.Leads.Where(m => m.Company.Company.Contains(filtertext)); 
            break;
          case "contact":
            leads = db.Leads.Where(m => m.Contact.Surname.Contains(filtertext));
            break;
          case "postcode":
            leads = db.Leads.Where(m => m.Territory.Contains(filtertext));
            break;
          case "alias":
            leads = db.Leads.Where(m => m.IntRef==filtertext);
            break;
          default:
            leads = db.Leads;
            break;
        }

        try
        {
            viewModel.Leads = leads.OrderBy(m => m.Diaries.FirstOrDefault().Scheduled).ToList();
        }
        catch (Exception)
        {
            viewModel.Leads = leads.OrderBy(m => m.SectorID).ToList();
        }
        
        viewModel.UserFilter = string.Empty;
        ViewBag.LeadStatusesID = new SelectList(db.LeadStatuses, "LeadStatusesID", "LeadStatus");

        int page = 1;
        int pageNumber = 1;
        var onePageOfLeads = viewModel.Leads.ToPagedList(pageNumber, 30);

        ViewBag.LeadsPage = onePageOfLeads;

        return PartialView("Index", viewModel);
      }

      public ActionResult CompanyRegistrations(int CompanyID)
      {
        ViewModelCompanies viewModel = new ViewModelCompanies();
        viewModel.ListCompanyRegistered = db.CompanyRegistered.Where(m => m.CompanyID == CompanyID).ToList();
        ViewBag.CompanyID = CompanyID;
        return PartialView(viewModel);
      }

      public ActionResult NewCompanyRegistration(int CompanyID)
      {
        CompanyRegistered companyRegistered = new CompanyRegistered();
        companyRegistered.CompanyID = CompanyID;

        return PartialView(companyRegistered);
      }

      [HttpPost]
      public ActionResult NewCompanyRegistration(CompanyRegistered companyRegistered)
      {
        db.CompanyRegistered.Add(companyRegistered);
        db.SaveChanges();
        return RedirectToAction("CompanyRegistrations", new {CompanyID=companyRegistered.CompanyID });
      }

      public ActionResult NewCompanyRestriction(int CompanyID)
      {
        CompanyRestrictions companyRestriction = new CompanyRestrictions();
        companyRestriction.CompanyID = CompanyID;

        return PartialView(companyRestriction);
      }

      [HttpPost]
      public ActionResult NewCompanyRestriction(CompanyRestrictions companyRestriction)
      {
        db.CompanyRestriction.Add(companyRestriction);
        db.SaveChanges();

        return RedirectToAction("CompanyRestrictions", new { CompanyID=companyRestriction.CompanyID});
      }

      public ActionResult NewCompanySundry(int CompanyID)
      {
        CompanySundries companySundry = new CompanySundries();
        companySundry.CompanyID = CompanyID;

        return PartialView(companySundry);
      }

      [HttpPost]
      public ActionResult NewCompanySundry(CompanySundries companySundry)
      {
        db.CompanySundries.Add(companySundry);
        db.SaveChanges();

        return RedirectToAction("CompanySundries", new { CompanyID = companySundry.CompanyID });
      }

      public ActionResult CompanyRestrictions(int CompanyID)
      {
        ViewModelCompanies viewModel=new ViewModelCompanies();
        viewModel.ListCompanyRestrictions = db.CompanyRestriction.Where(m => m.CompanyID == CompanyID).ToList();
        ViewBag.CompanyID = CompanyID;
        return PartialView("CompanyRestrictions",viewModel);
      }

      public ActionResult CompanySundries(int CompanyID)
      {
        ViewModelCompanies viewModel = new ViewModelCompanies();
        viewModel.ListCompanySundries = db.CompanySundries.Where(m => m.CompanyID == CompanyID).ToList();
        ViewBag.CompanyID = CompanyID;
        return PartialView(viewModel);
      }

      public ActionResult DeleteRegistration(int id)
      {
        CompanyRegistered cr = db.CompanyRegistered.Find(id);
        int companyID = cr.CompanyID;
        db.CompanyRegistered.Remove(cr);
        try
        {
          db.SaveChanges();
        }
        catch (Exception)
        {
          ViewBag.ErrorMessage = "You can not delete this because it is involved in previous results";
          return View("Errormessages");
        }
        return RedirectToAction("CompanyRegistrations", new { CompanyID=companyID });
      }

      public ActionResult DeleteRestriction(int id)
      {
        CompanyRestrictions cr = db.CompanyRestriction.Find(id);
        int companyID = cr.CompanyID;
        db.CompanyRestriction.Remove(cr);
        try
        {
        db.SaveChanges();
        }
        catch (Exception)
        {
          ViewBag.ErrorMessage = "You can not delete this because it is involved in previous results";
          return View("Errormessages");
        }
        return RedirectToAction("CompanyRestrictions", new { CompanyID = companyID });
      }

      public ActionResult DeleteSundry(int id)
      {
        CompanySundries cr = db.CompanySundries.Find(id);
        int companyID = cr.CompanyID;
        db.CompanySundries.Remove(cr);
        try
        {
        db.SaveChanges();
        }
        catch (Exception)
        {
          ViewBag.ErrorMessage = "You can not delete this because it is involved in previous results";
          return View("Errormessages");
        }

        return RedirectToAction("CompanySundries", new { CompanyID = companyID });
      }

      public ActionResult EditRegistration(int id)
      {
        CompanyRegistered cr = db.CompanyRegistered.Find(id);
        return PartialView(cr);
      }

      [HttpPost]
      public ActionResult EditRegistration(CompanyRegistered dataUpdate)
      {
        int companyID = dataUpdate.CompanyID;
        if (ModelState.IsValid)
        {
          db.Entry(dataUpdate).State = EntityState.Modified;
          db.SaveChanges();
          return RedirectToAction("CompanyRegistrations", new { CompanyID = companyID });
        }

        return RedirectToAction("CompanyRegistrations", new { CompanyID = companyID });
      }

      public ActionResult EditRestriction(int id)
      {
        CompanyRestrictions cr = db.CompanyRestriction.Find(id);
        return PartialView(cr);
      }

      [HttpPost]
      public ActionResult EditRestriction(CompanyRestrictions dataUpdate)
      {
        int companyID = dataUpdate.CompanyID;
        if (ModelState.IsValid)
        {
          db.Entry(dataUpdate).State = EntityState.Modified;
          db.SaveChanges();
          return RedirectToAction("CompanyRestrictions", new { CompanyID = companyID });
        }

        return RedirectToAction("CompanyRestrictions", new { CompanyID = companyID });
      }

      public ActionResult EditSundry(int id)
      {
        CompanySundries cr = db.CompanySundries.Find(id);
        return PartialView(cr);
      }

      [HttpPost]
      public ActionResult EditSundry(CompanySundries dataUpdate)
      {
        int companyID = dataUpdate.CompanyID;
        if (ModelState.IsValid)
        {
          db.Entry(dataUpdate).State = EntityState.Modified;
          db.SaveChanges();
          return RedirectToAction("CompanySundries", new { CompanyID = companyID });
        }

        return RedirectToAction("CompanySundries", new { CompanyID = companyID });
      }

      public ActionResult PurchaseOrder(int id)
      {
        try
        {
          ViewModelPurchaseOrders viewModel = new ViewModelPurchaseOrders();
          viewModel.Lead = db.Leads.Find(id);

          viewModel.ListPurchaseOrders = db.PurchaseOrders
            .Where(m => m.LeadID == id)
            .OrderBy(m => m.PID)
            .ToList();

            viewModel.POReportViewList = new List<POReportView>();

            foreach (var item in viewModel.ListPurchaseOrders)
            {
            POReportView poReportView = new POReportView();
            poReportView.Ref = item.PID;
            poReportView.CallCentre = item.CallCentre==null ? string.Empty : item.CallCentre.CallCentre;
            poReportView.VolumeOrdered = item.Quantity;
            poReportView.NewLeadsSupplied = item.TotalLeadsSupplied;
            poReportView.NewLeadDeficit = poReportView.VolumeOrdered - poReportView.NewLeadsSupplied;
            poReportView.Returns = item.Rejected ?? 0;
            poReportView.InvalidReturns = item.InvalidReturns ?? 0;
            poReportView.ReplacementsSupplied = item.Replaced ?? 0;
            poReportView.ReplacementsDeficit = poReportView.Returns - poReportView.ReplacementsSupplied -poReportView.InvalidReturns;
            poReportView.TotalDeficit = poReportView.NewLeadDeficit + poReportView.ReplacementsDeficit;
            poReportView.PurchaseOrderID = item.PurchaseOrderID;
            poReportView.ClientPaid = item.ClientPaid??false;
            poReportView.Visible = item.Visible??false;
              if (poReportView.TotalDeficit!=0)
              {
              viewModel.POReportViewList.Add(poReportView);
              viewModel.rT.VolumeOrdered += item.Quantity;
              viewModel.rT.NewLeadsSupplied += item.TotalLeadsSupplied ?? 0;
              viewModel.rT.NewLeadDeficit += poReportView.NewLeadDeficit ?? 0;
              viewModel.rT.Returns += poReportView.Returns;
              viewModel.rT.InvalidReturns += poReportView.InvalidReturns;
              viewModel.rT.ReplacementsSupplied += poReportView.ReplacementsSupplied;
              viewModel.rT.ReplacementsDeficit += poReportView.ReplacementsDeficit;
              viewModel.rT.TotalDeficit += poReportView.TotalDeficit ?? 0;
              }
            }
            viewModel.NewPurchaseOrders =new List<PurchaseOrders>();
            for (int i = 0; i < viewModel.Lead.TotalWeeks; i++)
            {
            PurchaseOrders pOrder = new PurchaseOrders();
            pOrder.LeadID = id;
            viewModel.NewPurchaseOrders.Add(pOrder);			
            }
          return PartialView("PurchaseOrder",viewModel);
        }
        catch (Exception e)
        {
          ViewBag.ErrorMessage = "Purchase Orders " + e.LineNumber().ToString() + " : " + e.Message + " - " + e.InnerException;
          return View("Errormessages");
        }
      }

      [HttpPost]
      public void PurchaseOrder(ViewModelPurchaseOrders viewModel)
      {
        foreach (PurchaseOrders purchaseorder in viewModel.NewPurchaseOrders)
        {
          if (purchaseorder.PID != null)
          {
            purchaseorder.PID = purchaseorder.PID.ToUpper();
            purchaseorder.Accepted = 0;
            purchaseorder.Deficit = 0;
            purchaseorder.Replaced = 0;
            purchaseorder.Rejected = 0;
            db.PurchaseOrders.Add(purchaseorder);
            db.SaveChanges();
          }
        }
      }

      public ActionResult CreatePurchaseOrder(int leadID)
      {
        ViewModelPurchaseOrders viewModel=new ViewModelPurchaseOrders();
        viewModel.Lead = db.Leads.Find(leadID);
        // how many call centres involved
        int callCentres = 1;
        if (viewModel.Lead.CallCentre2ID != null) { callCentres++; };
        if (viewModel.Lead.CallCentre3ID != null) { callCentres++; };
        if (viewModel.Lead.CallCentre4ID != null) { callCentres++; };
        if (viewModel.Lead.CallCentre5ID != null) { callCentres++; };

        //POprocessing.AllocatePO(viewModel.Lead.LeadID, viewModel.Lead.CallCentre1ID, callCentres, true);
        //if (viewModel.Lead.CallCentre2ID != null)
        //{
        //  POprocessing.AllocatePO(viewModel.Lead.LeadID, viewModel.Lead.CallCentre2ID ?? 0, callCentres, true);
        //}
        //if (viewModel.Lead.CallCentre3ID != null)
        //{
        //  POprocessing.AllocatePO(viewModel.Lead.LeadID, viewModel.Lead.CallCentre3ID ?? 0, callCentres, true);
        //}
        //if (viewModel.Lead.CallCentre4ID != null)
        //{
        //  POprocessing.AllocatePO(viewModel.Lead.LeadID, viewModel.Lead.CallCentre4ID ?? 0, callCentres, true);
        //}
        //if (viewModel.Lead.CallCentre5ID != null)
        //{
        //  POprocessing.AllocatePO(viewModel.Lead.LeadID, viewModel.Lead.CallCentre5ID ?? 0, callCentres, true);
        //}
        return RedirectToAction("PurchaseOrder", new { id=leadID });
      }

      [HttpPost]
      public void ChangePID(int id, string pid)
      {
        PurchaseOrders po=db.PurchaseOrders.Find(id);
        po.PID = pid;
        db.Entry(po).State = EntityState.Modified;
        db.SaveChanges();
      }

      [HttpPost]
      public void ChangeQty(int id, string qty)
      {
        PurchaseOrders po = db.PurchaseOrders.Find(id);
        po.Quantity = Convert.ToInt16(qty);
        db.Entry(po).State = EntityState.Modified;
        db.SaveChanges();
      }

      [HttpPost]
      public void ChangeNewLeadsSupplied(int id, string qty)
      {
        PurchaseOrders po = db.PurchaseOrders.Find(id);
        po.Accepted = Convert.ToInt16(qty);
        db.Entry(po).State = EntityState.Modified;
        db.SaveChanges();
      }

      [HttpPost]
      public void ChangeReturns(int id, string qty)
      {
      PurchaseOrders po = db.PurchaseOrders.Find(id);
      po.Rejected = Convert.ToInt16(qty);
      db.Entry(po).State = EntityState.Modified;
      db.SaveChanges();
      }

      [HttpPost]
      public void ChangeInvalidReturns(int id, string qty)
      {
      PurchaseOrders po = db.PurchaseOrders.Find(id);
      po.InvalidReturns = Convert.ToInt16(qty);
      db.Entry(po).State = EntityState.Modified;
      db.SaveChanges();
      }

      [HttpPost]
      public void ChangeReplacementsSupplied(int id, string qty)
      {
      PurchaseOrders po = db.PurchaseOrders.Find(id);
      po.Replaced = Convert.ToInt16(qty);
      db.Entry(po).State = EntityState.Modified;
      db.SaveChanges();
      }

      [HttpPost]
      public void ChangeAuthoriseFollowingPO(int id, int qty)
      {
      PurchaseOrders po = db.PurchaseOrders.Find(id);
      po.AuthoriseFollowingPO = qty == 1 ? true : false ;
      db.Entry(po).State = EntityState.Modified;
      db.SaveChanges();
      }

      [HttpPost]
      public void ChangePOVisibility(int id, int qty)
      {
        PurchaseOrders po = db.PurchaseOrders.Find(id);
        po.Visible = qty == 1 ? true : false;
        db.Entry(po).State = EntityState.Modified;
        db.SaveChanges();
      }

      [HttpPost]
      public void ChangeClientPaid(int id, int qty)
      {
          PurchaseOrders po = db.PurchaseOrders.Find(id);
          po.ClientPaid = qty == 1 ? true : false;
          db.Entry(po).State = EntityState.Modified;
          db.SaveChanges();
      }

	  public ActionResult AllocatePurchaseOrder(int id, string Surname=null, string Postcode=null)
	  {
      try
      {
	      ViewModelLeads viewModel = new ViewModelLeads();
	      viewModel.Lead = db.Leads.Where(m => m.LeadID == id).FirstOrDefault();
        var leadDates = (from ls in db.LeadStatus select new { ls.LeadDate }).Take(2);
        if (Surname == null && Postcode == null)
        {
          leadDates = from ls in db.LeadStatus
                          where ls.LeadID == viewModel.Lead.LeadID
                          where ls.CallCentreID != null
                          select new { ls.LeadDate };
        }
        else
        {
          Postcode = Postcode == null ? "*" : Postcode;
          Surname = Surname == null ? "*" : Surname;
          leadDates = from ls in db.LeadStatus
                          join lr in db.LeadResults on new { ls.LeadID, ls.BlockID } equals new { LeadID = lr.LeadID, BlockID = lr.BlockId }
                          where (lr.Question == "Surname" && lr.Answer.Contains(Surname)) ||
                                    (lr.Question == "Postcode" && lr.Answer.Contains(Postcode))
                          where ls.LeadID == viewModel.Lead.LeadID
                          where ls.CallCentreID != null
                          select new { ls.LeadDate };
        }

	      var lds = (from ld in leadDates.AsEnumerable()
				      select new { leadtimestamp = ld.LeadDate, orderDate = ld.LeadDate.Substring(6, 4) + ld.LeadDate.Substring(3, 2) + ld.LeadDate.Substring(0, 2) })
						      .Distinct();

	      List<string> leadtimestamps = lds.OrderByDescending(m => m.orderDate).Select(m => m.leadtimestamp).ToList();
	      ViewBag.IntRef = viewModel.Lead.IntRef;
	      ViewBag.LeadID = viewModel.Lead.LeadID;
	      Maintenance.inMaintenance = true;
      return View(leadtimestamps);
      }
      catch (Exception e)
      {
          ViewBag.ErrorMessage = "PO Allocation " + e.LineNumber().ToString() + " : " + e.Message + " - " + e.InnerException ;
          return View("Errormessages");
      }
	  }

    [HttpPost]
    public ActionResult AllocatePurchaseOrder(string txtsurname="", string txtpostcode="", int id=0)
    {
      return RedirectToAction("AllocatePurchaseOrder", new { id = id, Surname=txtsurname, Postcode=txtpostcode });
    }

	  public ActionResult ShowLeadsByDate(string leadDate, int id, int indexer, string Surname="", string Postcode="")
	  {
		  int testrecs = 0;
		  try
		  {
        Postcode = Postcode == null ? "*" : Postcode;
        Surname = Surname == null ? "*" : Surname;
			  ViewModelClient viewModel = new ViewModelClient();
			  viewModel.Leads = db.Leads.Where(m => m.LeadID == id).FirstOrDefault();
			  leadDate = leadDate.Substring(1, 10);
			  List<LeadResults> leadResults = db.LeadResults
				  .Join(db.LeadStatus, a => a.LeadID, b => b.LeadID, (a, b) => new { a, b })
				  .Where(m => m.a.LeadID == m.b.LeadID && m.a.BlockId == m.b.BlockID)
				  .Where(m => m.a.LeadID == viewModel.Leads.LeadID)
				  .Where(m => m.b.LeadDate == leadDate)
				  .Select(m => m.a)
				  .OfType<LeadResults>()
				  .ToList();
			  int blockID = 0;
			  LeadStatus statusBlock = db.LeadStatus.FirstOrDefault();
			  LeadFlags leadFlag = db.LeadFlags.FirstOrDefault();
			  LeadReasons leadReason = db.LeadReasons.FirstOrDefault();
        ViewBag.LeadStatusesID = new SelectList(db.LeadStatuses, "LeadStatusesID", "LeadStatus");
			  viewModel.FullListLeadResults = new List<ListLeadResults>();
			  testrecs = leadResults.Count();
			  viewModel.LeadDate = leadResults.LastOrDefault().LeadDate;
			  int totalLeads = 0;
			  foreach (LeadResults item in leadResults)
			  {
				  ListLeadResults listLeadResults = new ListLeadResults();
				  listLeadResults.LeadResult = item;
				  if (item.BlockId != blockID)
				  {
					  statusBlock = db.LeadStatus
						  .Where(m => m.LeadID == item.LeadID && m.BlockID == item.BlockId)
						  .FirstOrDefault();
					  leadFlag = db.LeadFlags.Find(statusBlock.LeadFlagsID);
					  leadReason = db.LeadReasons.Find(statusBlock.LeadReasonsID);
					  totalLeads++;
				  }
				  blockID = item.BlockId;
				  listLeadResults.LeadStatus = statusBlock;
				  listLeadResults.LeadFlag = leadFlag;
				  listLeadResults.LeadReason = leadReason;

				  viewModel.FullListLeadResults.Add(listLeadResults);
			  }
			  viewModel.Titles = new List<string>();

			  if (viewModel.FullListLeadResults.Count() > 0)
			  {
				  int questionCounter = 0;
				  foreach (var item in leadResults)
				  {
					  viewModel.Titles.Add(item.Question);
					  questionCounter++;
					  if (questionCounter == item.QuestionBlock)
					  {
						  questionCounter = 0;
						  break;
					  }
				  }
			  }
			  viewModel.PurchaseOrdersList = db.PurchaseOrders
					.Where(m => m.LeadID == viewModel.Leads.LeadID)
					.Select(m => new { m.PurchaseOrderID, m.PID })
					.ToList()
					.Select(p => new SelectListItem
					{
						Value = p.PurchaseOrderID.ToString(),
						Text = p.PID
					});

			  ViewBag.TotalLeads = totalLeads;
              ViewBag.Indexer = indexer;
			  return PartialView("PartialShowLeads", viewModel);
		  }
		  catch (Exception e)
		  {
			  ViewBag.ErrorMessage = "Showing Leads " + e.LineNumber().ToString() + " : " + e.Message + " - " + e.InnerException + " - num of recs=" + testrecs.ToString();
			  return View("Errormessages");
		  }
	  }

    
      public void ReallocatePID(int leadStatusID, int pid)
	  {
          if (pid>0)
          {
          LeadStatus leadStatus = db.LeadStatus.Where(m => m.LeadStatusID == leadStatusID).First();
          leadStatus.PurchaseOrderID = pid;
          db.Entry(leadStatus).State = EntityState.Modified;
          db.SaveChanges();
          }
	  }

      public void ReallocateReplacement(int leadStatusID, int replacement)
      {
        LeadStatus leadStatus = db.LeadStatus.Where(m => m.LeadStatusID == leadStatusID).First();
        if (replacement==0)
        {
            leadStatus.Replacement = null;    
        }
        else
        {
            leadStatus.Replacement = true;
        }
        
        db.Entry(leadStatus).State = EntityState.Modified;
        db.SaveChanges();
      }

      public ActionResult DeleteLead(int id)
      {
          LeadStatus leadStatus = db.LeadStatus.Find(id);
          int leadID=leadStatus.LeadID;
          List<LeadResults> leadResults = db.LeadResults.Where(m => m.LeadID == leadStatus.LeadID && m.BlockId == leadStatus.BlockID).ToList();
          foreach (var item in leadResults)
          {
              db.LeadResults.Remove(item);
          }
          db.LeadStatus.Remove(leadStatus);
          db.SaveChanges();
          return RedirectToAction("AllocatePurchaseOrder", new {id=leadID });
      }

      public ActionResult TestEmail()
      {
        try
        {
          MailMessage eMail = new MailMessage();
          eMail.From = new MailAddress("orac@dunsmorecrm.co.uk");
          eMail.To.Add("karl@dunsmoremarketing.co.uk");
          eMail.Subject = "Test Email";
          eMail.Body = "Test";

          NetworkCredential mailAuthentication = new NetworkCredential("orac@dunsmorecrm.co.uk", "20hamlet");
          SmtpClient SMTP = new SmtpClient("mail.dunsmorecrm.co.uk",25);
          SMTP.UseDefaultCredentials = false;
          SMTP.Credentials = mailAuthentication;

          SMTP.Send(eMail); 
        return RedirectToAction("Index");
        }
        catch (Exception e)
        {
          ViewBag.ErrorMessage = "Send Email " + e.LineNumber().ToString() + " : " + e.Message + " - " + e.InnerException;
          return View("Errormessages");
        }

      }

      public string DavidAlerts()
      {
        try
        {
          OracMailMessage mm = new OracMailMessage(true);
        //mm.EmailTo = "karl@dunsmoremarketing.co.uk";
        //mm.cc = "karlosmalin@gmail.com";
        mm.cc = "karl@dunsmoremarketing.co.uk";
        // No leads sent 
        List<CallCentres> listCallCentres = db.CallCentres
                                          .Where(o => o.active==true && o.Email1!=null)
                                          .ToList();
        string today = DateTime.Now.ToShortDateString();
        string tomorrow = DateTime.Now.AddDays(1).ToShortDateString();
        int leadStatusCount = 0;
        string IntRef = string.Empty;
        bool NoLeads = false;
        foreach (var item in listCallCentres)
        {
          mm.EmailTo = item.Email1;
          // No leads supplied to current Purchase Orders
          mm.EmailSubject = "No Leads Supply To Current Active Purchase Orders By " + item.CallCentre;
          mm.EmailBody = "The following Clients have not received any new leads in the last 24 hours<br>";
          List<PurchaseOrders> purchaseOrders = db.PurchaseOrders
            .Where(m => m.CallCentreID == item.CallCentreID)
            .Where(m => m.Deficit > 0 && m.Visible == true && m.WeekBeginning<DateTime.Now).ToList();
          NoLeads = false;
          foreach (var POs in purchaseOrders)
          {
              leadStatusCount = db.LeadStatus.Where(m => m.PurchaseOrderID == POs.PurchaseOrderID && (m.LeadDate == today || m.LeadDate == tomorrow)).Count();
              if (leadStatusCount==0)
              {
              IntRef = db.Leads.Find(POs.LeadID).IntRef;
              mm.EmailBody += "Client : " + IntRef + ". PO " + POs.PID + "<br>";
              NoLeads = true;
              }
          }
          mm.EmailBody += "This is an automated notification by Orac - please do not reply";
          if (NoLeads)
          {
            mm.SendEmail();          
          }
        }

        // Severe quality issues
        mm.EmailTo = "karl@dunsmoremarketing.co.uk";
        var purchasOrders = db.PurchaseOrders
                .Join(db.Leads, a => a.LeadID, b => b.LeadID, (a, b) => new { a, b })
                .Where(m => m.a.Visible == true && m.a.Quantity > 0 && m.a.Deficit > 0 && m.a.terminated==null)
                .Select(m => new { m.b.IntRef, m.a.TotalLeadsSupplied, m.a.Rejected, m.a.InvalidReturns, m.a.CallCentreID, m.a.PID });
        bool isAny = false;
        foreach (var item in listCallCentres)
        {
        mm.EmailSubject = "Warning : Quality Issues With " + item.CallCentre;
        mm.EmailBody = "Urgent Attention Required With Leads Supplied To The Following Clients<br>";
        isAny = false;
        foreach (var po in purchasOrders.Where(m => m.CallCentreID==item.CallCentreID))
        {
            decimal returnRatio = (po.Rejected - po.InvalidReturns ?? 0m) / ((po.TotalLeadsSupplied ?? 1m) == 0 ? 0.1m : (po.TotalLeadsSupplied ?? 1m)) * 100;
            if (returnRatio > 60m)
            {
            isAny = true;
            mm.EmailBody += "Client : " + po.IntRef + "<br>";
            mm.EmailBody += "Purchase Order : " + po.PID + "<br>";
            mm.EmailBody += String.Format("{0}{1:N2}{2}","% Returns : ", returnRatio,"<br><hr>");
            }
        }
        if (isAny)
        {
            mm.EmailBody += "This is an automated notification by Orac - please do not reply<br>";
            mm.SendEmail(); 
        }
        }
        return "Email Sent";
        }
        catch (Exception e)
        {
          string ErrorMessage = "Send Email " + e.LineNumber().ToString() + " : " + e.Message + " - " + e.InnerException;
          return ErrorMessage;
        }
      }

      [System.Web.Mvc.OutputCache(NoStore = true, Duration = 0, VaryByParam = "*")]
      public FileStreamResult ShowPDF(string param1, string pdfname, string reportname = "CallCentreStats")
      {
        // Create a new proxy to the web service
        rsExecService.ReportExecutionService rsExec = new rsExecService.ReportExecutionService();
        // Authenticate to the web service using windows credentials
        rsExec.Credentials = new System.Net.NetworkCredential(ConfigurationManager.AppSettings["UserID"], ConfigurationManager.AppSettings["Pwd"]);
        // Load the selected report
        rsExec.LoadReport("/dunsmore/" + reportname, null);

        // Prepare Render arguments
        string encoding = String.Empty;
        string mimeType = String.Empty;
        string extension = String.Empty;
        rsExecService.Warning[] warnings = null;
        string[] streamIDs = null;

        // Prepare report parameters.
        rsExecService.ParameterValue[] parameters = new rsExecService.ParameterValue[1];
        parameters[0] = new rsExecService.ParameterValue();
        parameters[0].Value = param1; 
        parameters[0].Name = "leadid";

        // Set the parameters for the report needed.
        rsExec.SetExecutionParameters(parameters, "en-us");

        //Render the report to a byte[]
        Byte[] byteInfo = rsExec.Render("PDF", null, out extension, out encoding, out mimeType, out warnings, out streamIDs);

        // Return the report as a file stream
        System.IO.MemoryStream ms = new System.IO.MemoryStream();
        ms.Write(byteInfo, 0, byteInfo.Length);
        ms.Position = 0;

        HttpContext.Response.AddHeader("content-disposition", "attachment; filename=" + pdfname + ".pdf");

        return new FileStreamResult(ms, ConfigurationManager.AppSettings["ReportName"]);

      }
      
      public void UpdateStatus(int leadID, int leadStatusesID)
      {
        Leads currentLead = db.Leads.Find(leadID);
        int holdingStatus = currentLead.LeadStatusesID;
        currentLead.LeadStatusesID = leadStatusesID;
        db.Entry(currentLead).State = EntityState.Modified;
        Enquiries enquiry = new Enquiries();
        enquiry.LeadID = leadID;
        enquiry.Notes = "The Pipeline Status has changed to " + db.LeadStatuses.Find(leadStatusesID).LeadStatus;
        enquiry.EnquiryDate = DateTime.Now;
        enquiry.CompanyID = currentLead.CompanyID;
        enquiry.ContactID = currentLead.ContactID;
        enquiry.EnquirySource = "O";
        enquiry.UserID = (Guid)Membership.GetUser().ProviderUserKey;
        db.Enquiry.Add(enquiry);
        db.SaveChanges();
    }

      public ActionResult Chasers()
      {
          var query = "exec chasers";
          GenericRepository gr = new GenericRepository();
          
          return View(gr.GetChasers(query));

      }

      public void UpdateAction(int leadid, string leadaction)
      {
        Leads lead = db.Leads.Find(leadid);
        lead.ActionRequired = leadaction == "1" ? true : false;
        db.Entry(lead).State = EntityState.Modified;
        db.SaveChanges();
      }

      public ActionResult NextRef()
      {
          GenericRepository gr = new GenericRepository();
          string query = "exec NextRef";
          List<NextRef> nextRefs = gr.GetNextRef(query);
          return View(nextRefs);
      }

      public ActionResult Survey(int leadID)
      {
        Maintenance.inMaintenance = true;
        List<Surveys> Surveys = db.Surveys.Where(m => m.LeadID == leadID).ToList();
        ViewBag.Company=db.Leads.Where(m => m.LeadID==leadID).First().Company.Company;
        ViewBag.LeadID = leadID;

        return View(Surveys);
      }

      public ActionResult CreateSurveyMapping(int leadID, int leadTemplateID=0)
      {
        Maintenance.inMaintenance = true;
        ViewModelSurveys ViewModel = new ViewModelSurveys();
        ViewModel.Surveys = new Surveys();
        ViewModel.Surveys.LeadID = leadID;
        ViewModel.ListLeadTemplates = db.LeadTemplate.ToList();
        if (leadTemplateID==0)
        {
          ViewModel.LeadTemplate = db.LeadTemplate.OrderBy(m => m.Title).FirstOrDefault();  
        }
        else
        {
          ViewModel.LeadTemplate = db.LeadTemplate.Where(m => m.LeadTemplateID == leadTemplateID).FirstOrDefault();
        }
        
        ViewModel.LeadStat = db.LeadStat.Where(m => m.LeadTemplateID == ViewModel.LeadTemplate.LeadTemplateID).OrderBy(m => m.DisplayOrder).FirstOrDefault();
        ViewModel.LeadStatQuestions = db.LeadStatQuestions.Where(m => m.LeadStatID == ViewModel.LeadStat.LeadStatID).OrderBy(m => m.DisplayOrder).FirstOrDefault();

        ViewBag.LeadTemplatesID = new SelectList(db.LeadTemplate.OrderBy(m => m.Title),"LeadTemplateID","Title",leadTemplateID) ;

        ViewModel.ListLeadStatQuestions = db.LeadStatQuestions
            .Join(db.LeadStat, a=> a.LeadStatID, b => b.LeadStatID, (a,b) => new { a,b })
            .Where(m => m.a.LeadStatID==m.b.LeadStatID)
            .Where(m => m.b.LeadTemplateID== ViewModel.LeadTemplate.LeadTemplateID)
            .Select(m => m.a)
            .ToList() ;

        ViewModel.LeadStatQuestionsCheckboxes = new List<bool>();
        ViewModel.LeadStatQuestionIDs = new List<int>();
        ViewModel.LeadStatQuestionsAnswers = new List<string>();
        ViewModel.LeadStatQuestionsMinLength = new List<string>();
        for (int i = 0; i < ViewModel.ListLeadStatQuestions.Count(); i++)
        {
          ViewModel.LeadStatQuestionsCheckboxes.Add(false);
          ViewModel.LeadStatQuestionsAnswers.Add(string.Empty);
          ViewModel.LeadStatQuestionsMinLength.Add(string.Empty);
          ViewModel.LeadStatQuestionIDs.Add(ViewModel.ListLeadStatQuestions[i].LeadStatQuestionsID);
        }

        if (leadTemplateID==0)
        {
          leadTemplateID = db.LeadTemplate.OrderBy(m => m.Title).FirstOrDefault().LeadTemplateID;
        }
        ViewModel.LeadTemplatesID = leadTemplateID;

        return View(ViewModel);
      }

      [HttpPost]
      public ActionResult CreateSurveyMapping(ViewModelSurveys viewModel)
      {

        Surveys survey = new Surveys();
        survey.Ref = viewModel.Surveys.Ref;
        survey.LeadID = viewModel.Surveys.LeadID;
        survey.HttpPost = viewModel.Surveys.HttpPost;
        survey.HTTPstring = viewModel.Surveys.HTTPstring;
        survey.LeadTemplateID = viewModel.LeadTemplatesID;
        db.Surveys.Add(survey);

        for (int i = 0; i < viewModel.LeadStatQuestionsCheckboxes.Count(); i++)
        {
          if (viewModel.LeadStatQuestionsCheckboxes[i]==true)
          {
            // corresponding value in lead stat questions 
            SurveyMappings surveyMappings = new SurveyMappings();
            surveyMappings.Survey = survey;
            surveyMappings.LeadStatQuestionsID = viewModel.LeadStatQuestionIDs[i];
            LeadStatQuestions lsq = db.LeadStatQuestions.Find(viewModel.LeadStatQuestionIDs[i]);
            if (lsq.AnswerType=="L")
            {
              surveyMappings.Answer = "Yes";
            }
            else
            {
              surveyMappings.Answer = viewModel.LeadStatQuestionsAnswers[i];
            }
            if (viewModel.LeadStatQuestionsMinLength[i]!=null && viewModel.LeadStatQuestionsMinLength[i].Length>0)
            {
              surveyMappings.MinLength = Convert.ToByte(viewModel.LeadStatQuestionsMinLength[i]);  
            }
            db.SurveyMappings.Add(surveyMappings);
          }
        }

        db.SaveChanges();

        return RedirectToAction("Index", "Home");
      }

      public ActionResult EditSurvey(int id)
      {
        Maintenance.inMaintenance = true;
        ViewModelSurveys ViewModel = new ViewModelSurveys();
        ViewModel.Surveys = db.Surveys.Find(id);
        ViewModel.ListLeadTemplates = db.LeadTemplate.ToList();
        ViewModel.LeadTemplate = db.LeadTemplate.Where(m => m.LeadTemplateID == ViewModel.Surveys.LeadTemplateID).FirstOrDefault();
        ViewModel.LeadStat = db.LeadStat.Where(m => m.LeadTemplateID == ViewModel.LeadTemplate.LeadTemplateID).OrderBy(m => m.DisplayOrder).FirstOrDefault();
        ViewModel.LeadStatQuestions = db.LeadStatQuestions.Where(m => m.LeadStatID == ViewModel.LeadStat.LeadStatID).OrderBy(m => m.DisplayOrder).FirstOrDefault();

        ViewBag.LeadTemplatesID = db.LeadTemplate
                       .OrderBy(m => m.Title)
                       .ToList()
                             .Select(c => new SelectListItem
                             {
                               Value = c.LeadTemplateID.ToString(),
                               Text = c.Title
                             });


        ViewModel.ListLeadStatQuestions = db.LeadStatQuestions
            .Join(db.LeadStat, a => a.LeadStatID, b => b.LeadStatID, (a, b) => new { a, b })
            .Where(m => m.a.LeadStatID == m.b.LeadStatID)
            .Where(m => m.b.LeadTemplateID == ViewModel.LeadTemplate.LeadTemplateID)
            .Select(m => m.a)
            .ToList();

        ViewModel.LeadStatQuestionsCheckboxes = new List<bool>();
        ViewModel.LeadStatQuestionIDs = new List<int>();
        ViewModel.LeadStatQuestionsAnswers = new List<string>();
        ViewModel.LeadStatQuestionsMinLength = new List<string>();
        int lsqID=0;
        string savedAnswer;
        for (int i = 0; i < ViewModel.ListLeadStatQuestions.Count(); i++)
        {
          lsqID = ViewModel.ListLeadStatQuestions[i].LeadStatQuestionsID;
          if (db.SurveyMappings.Where(m => m.SurveyID==ViewModel.Surveys.SurveyID && m.LeadStatQuestionsID==lsqID).Any())
          {
            ViewModel.LeadStatQuestionsCheckboxes.Add(true);
            savedAnswer = db.SurveyMappings
                .Where(m => m.LeadStatQuestionsID == lsqID && m.SurveyID == id)
                .FirstOrDefault()
                .Answer;
            ViewModel.LeadStatQuestionsAnswers.Add(savedAnswer==null ? string.Empty : savedAnswer);
            string minLength = (db.SurveyMappings
                .Where(m => m.LeadStatQuestionsID == lsqID && m.SurveyID == id)
                .FirstOrDefault()
                .MinLength).ToString();
            ViewModel.LeadStatQuestionsMinLength.Add(minLength);
          }
          else
          {
            ViewModel.LeadStatQuestionsCheckboxes.Add(false);
            ViewModel.LeadStatQuestionsAnswers.Add(string.Empty);
            ViewModel.LeadStatQuestionsMinLength.Add(string.Empty);
          }
          ViewModel.LeadStatQuestionIDs.Add(ViewModel.ListLeadStatQuestions[i].LeadStatQuestionsID);
        }

        return View(ViewModel);
      }

      [HttpPost]
      public ActionResult EditSurvey(ViewModelSurveys viewModel)
      {
        Surveys survey = db.Surveys.Find(viewModel.Surveys.SurveyID);
        survey.Criteria = viewModel.Surveys.Criteria;
        survey.HttpPost = viewModel.Surveys.HttpPost;
        survey.OutputFileName = viewModel.Surveys.OutputFileName;
        db.Entry(survey).State = EntityState.Modified;

        for (int i = 0; i < viewModel.LeadStatQuestionsCheckboxes.Count(); i++)
        {
          int lsqID = 0;
          lsqID = viewModel.LeadStatQuestionIDs[i];
          LeadStatQuestions lsq = db.LeadStatQuestions.Find(viewModel.LeadStatQuestionIDs[i]);
          if (viewModel.LeadStatQuestionsCheckboxes[i] == true)
          {
            if (db.SurveyMappings.Where(m => m.SurveyID==survey.SurveyID && m.LeadStatQuestionsID==lsqID).Count()==0)
            {
              // add the mapping
              SurveyMappings surveyMappings = new SurveyMappings();
              surveyMappings.Survey = survey;
              surveyMappings.LeadStatQuestionsID = viewModel.LeadStatQuestionIDs[i];
              if (viewModel.LeadStatQuestionsMinLength[i].Length > 0)
              {
                surveyMappings.MinLength = Convert.ToByte(viewModel.LeadStatQuestionsMinLength[i]);
              }
              if (lsq.AnswerType == "L")
              {
                surveyMappings.Answer = "Yes";
              }
              else
              {
                surveyMappings.Answer = viewModel.LeadStatQuestionsAnswers[i];
              }

              db.SurveyMappings.Add(surveyMappings);
            }
            else if (db.SurveyMappings.Where(m => m.SurveyID == survey.SurveyID && m.LeadStatQuestionsID == lsqID).Count() == 1)
            {
              // the response has been changed
              SurveyMappings sm = db.SurveyMappings.Where(m => m.SurveyID == survey.SurveyID && m.LeadStatQuestionsID == lsqID).FirstOrDefault();
              if (viewModel.LeadStatQuestionsMinLength[i].Length>0)
              {
                sm.MinLength = Convert.ToByte(viewModel.LeadStatQuestionsMinLength[i]);  
              }
              if (lsq.AnswerType == "L")
              {
                sm.Answer = "Yes";
              }
              else
              {
                sm.Answer = viewModel.LeadStatQuestionsAnswers[i];
              }

              db.Entry(sm).State = EntityState.Modified;             
            }
          }
          else
          {
            // remove the mapping
            SurveyMappings surveyMappings = db.SurveyMappings
                .Where(m => m.SurveyID == survey.SurveyID && m.LeadStatQuestionsID == lsqID)
                .FirstOrDefault();
            if (surveyMappings!=null)
            {
              db.SurveyMappings.Remove(surveyMappings);
            }
          }  
        }

        db.SaveChanges();

        return RedirectToAction("Survey", new  { leadid=viewModel.Surveys.LeadID});
      }


      public ActionResult ExportMapping(int surveyID)
      {
        Maintenance.inMaintenance = true;
        ViewModelSurveys viewModel=new ViewModelSurveys();
        viewModel.Surveys = db.Surveys.Find(surveyID);
        ViewBag.SurveyMappingsID = new SelectList(
            (db.SurveyMappings
            .Where(m => m.SurveyID == surveyID)
            .Select(m => new { m.SurveyMappingsID, m.LeadStatQuestion.Question }))
            , "SurveyMappingsID", "Question");
        viewModel.ListExportMappings = db.ExportMappings.Where(m => m.SurveyID == surveyID).OrderBy(m => m.ExportOrder).ToList();
        string contactFields="Title,Forename,Initials,Surname,Address1,TownCity,County,Country,Telephone,Email,Mobile,PostCode";
        ViewBag.SurveyContactField = new SelectList( contactFields.Split(',').ToList());
        viewModel.ClientField = string.Empty;
        return View(viewModel);
      }

      [HttpPost]
      public ActionResult ExportMapping(ViewModelSurveys viewModel, bool? chkAddThis, string SurveyContactField, string SurveyMappingsID, string chkBoxRemove)
      {
        Maintenance.inMaintenance = true;
        if (viewModel.ListExportMappings!=null)
        {
          foreach (ExportMappings item in viewModel.ListExportMappings)
          {
            db.Entry(item).State = EntityState.Modified;
          }
        }
        if (chkAddThis==true)
        {
          ExportMappings exportMapping = new ExportMappings();
          exportMapping.SurveyID = viewModel.Surveys.SurveyID;
          if (SurveyContactField != "")
          {
            // add a contact field
            exportMapping.SurveyContactField = SurveyContactField;
          }
          else if (SurveyMappingsID != "")
          {
            exportMapping.SurveyMappingsID = Convert.ToInt16(SurveyMappingsID);
          }
          else if (viewModel.ClientField != null)
          {
            exportMapping.ClientField = viewModel.ClientField;
          }
          db.ExportMappings.Add(exportMapping);
        }
        if (chkBoxRemove!=null)
        {
          ExportMappings em = db.ExportMappings.Find(Convert.ToInt16(chkBoxRemove));
          byte exportOrder=em.ExportOrder??99;
          db.ExportMappings.Remove(em);
          foreach (ExportMappings item in db.ExportMappings.Where(m => m.SurveyID==em.SurveyID && m.ExportOrder>exportOrder))
          {
            item.ExportOrder--;
            db.Entry(item).State = EntityState.Modified;
          }
        }
        
        db.SaveChanges();
        return RedirectToAction("ExportMapping", new { surveyID=viewModel.Surveys.SurveyID });
      }

      public ActionResult DeleteSurvey(int id)
      {
        db.LeadStatus.RemoveRange(db.LeadStatus
          .Join(db.PurchaseOrders, a => a.PurchaseOrderID, b => b.PurchaseOrderID, (a, b) => new { a, b })
				  .Where(m => m.a.PurchaseOrderID == m.b.PurchaseOrderID)
          .Where(m => m.b.SurveyID==id)
          .Select(m => m.a)
          );
        db.PurchaseOrders.RemoveRange(db.PurchaseOrders.Where(m => m.SurveyID==id));
        Surveys survey=db.Surveys.Find(id);
        db.Surveys.Remove(survey);
        db.SaveChanges();
        return RedirectToAction("Index");
      }
  }
}